package org.termine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import org.termine.entity.Availability;

@ManagedBean
@ApplicationScoped
public class AvailabilityDAO extends BaseDAO {

	public void updateAvailability(Connection connection, Availability availability)
			throws Exception {
		PreparedStatement statement = null;
		String statementString = "update AVAILABILITY set AV_START = ?, AV_END = ? where AV_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setTimestamp(1, new Timestamp(availability.getStart().getTime()));
			statement.setTimestamp(2, new Timestamp(availability.getEnd().getTime()));
			statement.setInt(3, availability.getAvailability_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void addAvailability(Connection connection, Availability availability, Integer providerId)
			throws Exception {
		PreparedStatement statement = null;
		String statementString = "insert into AVAILABILITY (P_ID, AV_START, AV_END, AV_TYPE) values (?,?,?,?)";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setTimestamp(2, new Timestamp(availability.getStart().getTime()));
			statement.setTimestamp(3, new Timestamp(availability.getEnd().getTime()));
			statement.setString(4, availability.getType());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void deleteAvailability(Connection connection, Availability availability)
			throws Exception {
		PreparedStatement statement = null;
		String statementString = "delete from AVAILABILITY where AV_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, availability.getAvailability_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public boolean overlapsWithExistingAvailabilities(Connection connection,
			Availability availability, Integer providerId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select count(*) as X from AVAILABILITY where (AV_START <= ?) and (? <= AV_END) and P_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setTimestamp(1, new Timestamp(availability.getEnd().getTime()));
			statement.setTimestamp(2, new Timestamp(availability.getStart().getTime()));
			statement.setInt(3, providerId);
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			return resultSet.getInt("X") != 0;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public Integer getNumberOfCorrespondingAppointments(Connection connection,
			Availability availability) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select count(*) as X from AVAILABILITY av join PROVIDER p on av.AV_ID = p.P_ID "
				+ "join SERVICE s on p.P_ID = s.P_ID join APPOINTMENT ap on s.S_ID = ap.S_ID "
				+ "where ap.AP_START >= av.AV_START and ap.AP_START + s.S_DURATION_IN_MIN minutes <= av.AV_END "
				+ "and av.AV_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, availability.getAvailability_id());
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				return new Integer(resultSet.getInt("X"));
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public Integer getAvailabilityId(Connection connection, Availability availability,
			Integer providerId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select AV_ID from AVAILABILITY where P_ID = ? and AV_START = ? "
				+ "and AV_END = ? and AV_TYPE = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setTimestamp(2, new Timestamp(availability.getStart().getTime()));
			statement.setTimestamp(3, new Timestamp(availability.getEnd().getTime()));
			statement.setString(4, availability.getType());
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				return new Integer(resultSet.getInt("AV_ID"));
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public List<Availability> getAllCurrentAvailabilitiesForProvider(Connection connection,
			Integer providerId, String type) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select * from AVAILABILITY where P_ID = ? and AV_END > ? and AV_TYPE = ? order by AV_START asc";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			Date now = new Date();
			statement.setTimestamp(2, new Timestamp(now.getTime()));
			statement.setString(3, type);
			ResultSet resultSet = statement.executeQuery();
			List<Availability> result = new LinkedList<Availability>();
			while (resultSet.next()) {
				Availability availability = new Availability();
				Integer id;
				Date date;
				String string;
				id = new Integer(resultSet.getInt("AV_ID"));
				if (!resultSet.wasNull()) {
					availability.setAvailability_id(id);
				}
				id = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					availability.setProvider_id(id);
				}
				date = resultSet.getTimestamp("AV_START");
				if (!resultSet.wasNull()) {
					availability.setStart(date);
				}
				date = resultSet.getTimestamp("AV_END");
				if (!resultSet.wasNull()) {
					availability.setEnd(date);
				}
				string = resultSet.getString("AV_TYPE");
				if (!resultSet.wasNull()) {
					availability.setType(string);
				}
				result.add(availability);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public List<Availability> getAvailabilitiesForProvider(Connection connection, Integer providerId)
			throws Exception {
		PreparedStatement statement = null;
		String statementString = "select * from AVAILABILITY where P_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			ResultSet resultSet = statement.executeQuery();
			List<Availability> result = new LinkedList<Availability>();
			while (resultSet.next()) {
				Availability availability = new Availability();
				Integer id;
				Date date;
				String string;
				id = new Integer(resultSet.getInt("AV_ID"));
				if (!resultSet.wasNull()) {
					availability.setAvailability_id(id);
				}
				id = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					availability.setProvider_id(id);
				}
				date = resultSet.getTimestamp("AV_START");
				if (!resultSet.wasNull()) {
					availability.setStart(date);
				}
				date = resultSet.getTimestamp("AV_END");
				if (!resultSet.wasNull()) {
					availability.setEnd(date);
				}
				string = resultSet.getString("AV_TYPE");
				if (!resultSet.wasNull()) {
					availability.setType(string);
				}
				result.add(availability);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public Availability getAvailabilityForProvider(Connection connection, Integer providerId,
			Date from, Date to, String type) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select * from AVAILABILITY where P_ID = ? and AV_START < ? and "
				+ "AV_END > ? and AV_TYPE = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setTimestamp(2, new Timestamp(from.getTime()));
			statement.setTimestamp(3, new Timestamp(to.getTime()));
			statement.setString(4, type);
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				Availability availability = new Availability();
				Integer id;
				Date date;
				String string;
				id = new Integer(resultSet.getInt("AV_ID"));
				if (!resultSet.wasNull()) {
					availability.setAvailability_id(id);
				}
				id = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					availability.setProvider_id(id);
				}
				date = resultSet.getTimestamp("AV_START");
				if (!resultSet.wasNull()) {
					availability.setStart(date);
				}
				date = resultSet.getTimestamp("AV_END");
				if (!resultSet.wasNull()) {
					availability.setEnd(date);
				}
				string = resultSet.getString("AV_TYPE");
				if (!resultSet.wasNull()) {
					availability.setType(string);
				}
				return availability;
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

}
